﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using ElectronicJournal.DB.Item;

namespace ElectronicJournal.DB
{
    public class DbPublications
    {
        private SqlDb db;

        public DbPublications()
        {
            db = new SqlDb();
        }

        /*public bool CreateNewPublication(ItemPublication item)
        {
            string query = String.Format("INSERT INTO Publications VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', {17})",
                                            item.TitleRussian, item.TitleEnglish, item.ScientificDirection, item.Type, item.UDCIndex, item.Speciality, item.SummaryRussian, item.SummaryEnglish,
                                            item.KeywordsRussian, item.KeywordsEnglish, item.FileArticle, item.FileReview1, item.FileReview2, item.FileLetter, item.FileExpert, item.Date, item.State, item.Id);
            try
            {
                db.Query(query);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }*/

        public ItemPublication FetchRowPublication(DataRow row)
        {
            return new ItemPublication()
            {
                //AuthorId = row["AuthorId"].ToString(),
                TitleRussian = row["TitleRussian"].ToString(),
                TitleEnglish = row["TitleEnglish"].ToString(),
                ScientificDirection = row["ScientificDirection"].ToString(),
                Type = row["Type"].ToString(),
                UDCIndex = row["UDCIndex"].ToString(),
                Speciality = row["Speciality"].ToString(),
                SummaryRussian = row["SummaryRussian"].ToString(),
                SummaryEnglish = row["SummaryEnglish"].ToString(),
                KeywordsRussian = row["KeywordsRussian"].ToString(),
                KeywordsEnglish = row["KeywordsEnglish"].ToString(),
                FileArticle = row["FileArticle"].ToString(),
                FileLetter = row["FileLetter"].ToString(),
                FileExpert = row["FileExpert"].ToString(),
                Date = Convert.ToDateTime(row["Date"].ToString()),
                State = Convert.ToInt32(row["State"].ToString()),
                Id = row["Id"].ToString()
            };
        }

       
       

        /// <summary>
        /// Получить все публикации, ждущие оплаты
        /// </summary>
        /// <param name="AuthorId"></param>
        /// <returns></returns>
        public List<ItemPublication> GetPublicationsWaitingForPayment(string AuthorId)
        {
            List<ItemPublication> list = new List<ItemPublication>();
            //AuthorId
            string authToPublReq = String.Format("SELECT PubId FROM AuthorToPub authorsToPub inner join Publications pubs on authorsToPub.PubId = pubs.Id WHERE authorsToPub.AuthorId = '{0}' ", AuthorId);

            DataTable authToPublDTable = db.Read(authToPublReq);
            foreach (DataRow dataRow in authToPublDTable.Rows)
            {
                list.Add(GetPublicationById(dataRow[0].ToString()));
            }

            //string query = String.Format("SELECT * FROM Publications WHERE State = 0");
            //DataTable dt = db.Read(query);
            //foreach (DataRow row in dt.Rows)
            //{
            //    list.Add(FetchRowPublication(row));
            //}
            return list;
        }

        public int GetPublicationsWaitingForPaymentCount(string AuthorId)
        {
            return GetPublicationsWaitingForPayment(AuthorId).Count;
        }

        public ItemPublication GetPublicationById(string Id)
        {
            string query = String.Format("SELECT * FROM Publications WHERE Id = '{0}'", Id);
            DataTable dt = db.Read(query);
            if (dt.Rows.Count > 0)
            {
                return FetchRowPublication(dt.Rows[0]);
            }
            return null;
        }

        /*public bool UpdatePublication(ItemPublication item)
        {
            string query = String.Format("Update Publications SET TitleRussian = '{0}', TitleEnglish = '{1}', ScientificDirection = '{2}', Type = '{3}', UDCIndex = '{4}', Speciality = '{5}', SummaryRussian =  '{6}', SummaryEnglish = '{7}', KeywordsRussian = '{8}', KeywordsEnglish = '{9}', FileArticle = '{10}', FileReview1 = '{11}', FileReview2 = '{12}', FileLetter = '{13}', FileExpert = '{14}' WHERE Id = '{15}'",
                                            item.TitleRussian, item.TitleEnglish, item.ScientificDirection, item.Type, item.UDCIndex, item.Speciality, item.SummaryRussian, item.SummaryEnglish,
                                            item.KeywordsRussian, item.KeywordsEnglish, item.FileArticle, item.FileReview1, item.FileReview2, item.FileLetter, item.FileExpert, item.Id);
            try
            {
                db.Query(query);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }*/

        public void UpdatePublicationState(string Id, int State)
        {
            string query = String.Format("UPDATE Publications SET State = {0} WHERE Id = '{1}'", State, Id);
            db.Query(query);
        }

        public void AddPublicationToJournal(string PublicationId, string JournalId)
        {
            string query = String.Format("INSERT PublicationsInJournal VALUES ('{0}', '{1}')", PublicationId, JournalId);
            db.Query(query);
            UpdatePublicationState(PublicationId, 6);   //состояние "публикация напечатана"
        }

        public void DeletePublicationFromJournal(string PublicationId, string JournalId)
        {
            string query = String.Format("DELETE FROM PublicationsInJournal WHERE PublicationId = '{0}' AND JournalId = '{1}'", PublicationId, JournalId);
            db.Query(query);
            UpdatePublicationState(PublicationId, 5);   //сотояние "публикация готова"
        }

        

        public string GetUDCIndex(string Id)
        {
            string query = String.Format("SELECT UDC FROM UDC WHERE UDCId = '{0}'", Id);
            DataTable dt = db.Read(query);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["UDC"].ToString();
            }
            return null;
        }

        public string GetType(string Id)
        {
            string query = String.Format("SELECT Title FROM PublicationTypes WHERE Id = '{0}'", Id);
            DataTable dt = db.Read(query);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["Title"].ToString();
            }
            return null;
        }

        public string GetScientificDirection(String Id)
        {
            string query = String.Format("SELECT Title FROM ScientificDirections WHERE Id = '{0}'", Id);
            DataTable dt = db.Read(query);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["Title"].ToString();
            }
            return null;
        }

        public void SendPublicationToReviewer(string PublicationId, string ReviewerId)
        {
            string query = String.Format("INSERT INTO Review (ReviewReiewerId, ReviewArticleId) VALUES ({0}, '{1}')", ReviewerId, PublicationId);
            db.Query(query);
            UpdatePublicationState(PublicationId, 2);   //состояние "публикация отправлена рецензенту"
        }

        public int GetUnreadPublicationsCountForEditor()
        {
            string query = String.Format("SELECT Id FROM Publications WHERE State = 1 OR State = 3");
            DataTable dt = db.Read(query);
            return dt.Rows.Count;
        }

        public List<ItemPublication> GetPublicationsInJournal(string JouranlId)
        {
            List<ItemPublication> list = new List<ItemPublication>();
            string query = String.Format("SELECT Publications.* FROM Publications, PublicationsInJournal WHERE Publications.Id = PublicationsInJournal.PublicationId AND PublicationsInJournal.JournalId = '{0}'", JouranlId);
            DataTable table = db.Read(query);
            foreach (DataRow row in table.Rows)
            {
                list.Add(FetchRowPublication(row));
            }
            return list;
        }

        public string GetPublicationAuthors(string id)
        {
            string authors = "";
            string query = String.Format("SELECT Author.* FROM Author, AuthorToPub WHERE Author.AuthorId = AuthorToPub.AuthorId AND AuthorToPub.PubId = '{0}'", id);
            DataTable table = db.Read(query);
            foreach(DataRow row in table.Rows)
            {
                string tmp = "";
                if (authors.Length > 0)
                {
                    tmp = ", ";
                }
                tmp += row["AuthorFirstName"].ToString();
                if (row["AuthorSecondName"] != null)
                {
                    tmp += " " + row["AuthorSecondName"].ToString().Substring(0, 1) + ".";
                }
                if (row["AuthorThirdName"] != null)
                {
                    tmp += row["AuthorThirdName"].ToString().Substring(0, 1) + ".";
                }
                authors += tmp;
            }
            return authors;
        }

        public ItemJournal GetJournalOfPublication(string PublicationId)
        {
            string query = String.Format("SELECT Journal.* FROM Journal, PublicationsInJournal WHERE Journal.JournalId = PublicationsInJournal.JournalId AND PublicationsInJournal.PublicationId = '{0}'", PublicationId);
            DataTable dt = db.Read(query);
            if (dt.Rows.Count > 0)
            {
                return new ItemJournal()
                {
                    Year = Convert.ToInt32(dt.Rows[0]["JournalYear"].ToString()),
                    Number = Convert.ToInt32(dt.Rows[0]["JournalNumber"].ToString()),
                    Id = dt.Rows[0]["JournalId"].ToString()
                };
            }
            return null;
        }

        public List<ItemPublication> GetUnpublishedPublications()
        {
            List<ItemPublication> list = new List<ItemPublication>();
            string query = String.Format("SELECT * FROM Publications WHERE State = 9");
            DataTable table = db.Read(query);
            foreach (DataRow row in table.Rows)
            {
                list.Add(FetchRowPublication(row));
            }
            return list;
        }
    }
}